package com.sh.ssao.lottery.config.poi;

import com.sh.ssao.lottery.config.utils.StrUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * ********************************
 *
 * @author zhaoqingwei.
 * 天下英雄出我辈，一入江湖岁月催.
 * 20182018/12/2413:15
 * ********************************
 * 描述：
 * <p>
 * 注释：
 **/
public class ExcelUtil {
    /**
     *
     * 生成 xls
     * 通用list<Map<String Object>>
     * 表格属性列名数组 （第一行标题）
     * 需要显示的表格属性列名数组 如果是javabean 必须和字段名字一直 如果为Map 必须为Map的key名字对应
     * 需要显示的数据集合,集合泛型支持两种，1：符合javabean风格的类的对象 2：Map类型。此方法支持的
     * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param name
     * @param title    表格标题
     * @param headers  表格标题名
     * @param fields   表格字段名
     * @param data 表格数据
     * @return
     */
    public HSSFWorkbook exportExcel(String name, String title,String[] headers,String[]  fields,List<Map<String,Object>> data,String date) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);

        //白色背景色样式
        HSSFCellStyle stylewhite = workbook.createCellStyle();
        stylewhite.setWrapText(true);// 自动换行

        //白色背景色样式小标题
        HSSFCellStyle stylewhitele = workbook.createCellStyle();
        stylewhitele.setWrapText(true);// 自动换行

        //白色背景色样式headers
        HSSFCellStyle stylewhitehe= workbook.createCellStyle();
        stylewhitehe.setWrapText(true);// 自动换行

        //白色背景色样式内容
        HSSFCellStyle stylewhiteCon = workbook.createCellStyle();
        HSSFDataFormat formatcon = workbook.createDataFormat();
        stylewhiteCon.setDataFormat(formatcon.getFormat("@"));

        //标题-字体
        HSSFFont namefont = workbook.createFont();
        namefont.setFontName("宋体");
        namefont.setFontHeightInPoints((short) 15);// 字体大小

        //表头-字体
        HSSFFont headerfont = workbook.createFont();
        headerfont.setFontName("宋体");
        headerfont.setFontHeightInPoints((short) 10);// 字体大小
        headerfont.setBold(true);//设置字体加粗

        //表头-水平居中
        HSSFCellStyle styleheards = workbook.createCellStyle();
        styleheards.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());// 设置背景色
        styleheards.setFont(headerfont);//运用背景字体

        //表单-内容字体
        HSSFFont contentFont = workbook.createFont();
        contentFont.setFontHeightInPoints((short) 10);// 字体大小
        contentFont.setFontName("宋体");

        // 标题-把字体应用到当前的样式
        stylewhitehe.setFont(headerfont);
        stylewhite.setFont(namefont);
        stylewhite.setAlignment(HorizontalAlignment.CENTER);//水平居中

        //内容
        stylewhiteCon.setFont(contentFont);

        // 产生表格标题行
        HSSFRow row0 = sheet.createRow(0);
        row0.setHeight((short) 500); //设置行高
        HSSFCell cellname = row0.createCell(0);
        HSSFRichTextString textname = new HSSFRichTextString(name);
        cellname.setCellValue(textname);
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, headers.length-1);
        sheet.addMergedRegion(range);
        cellname.setCellStyle(stylewhite);
        int index=1;

        //统计时间生成
        if (headers.length > 2 ) {
            HSSFRow row1 = sheet.createRow(index++);
            row1.setHeight((short) 380); //设置行高
            int Celldate = 0;
            for (short i = 0; i < headers.length; i++) {
                if (i == headers.length-1) {
                    HSSFCell cell = row1.createCell(Celldate);
                    HSSFRichTextString text = new HSSFRichTextString(date);
                    cell.setCellValue("统计时间：" + text);
                    sheet.setColumnWidth(i, 7000);
                    cell.setCellStyle(stylewhiteCon);
                    Celldate ++ ;
                }else{
                    HSSFCell cell = row1.createCell(Celldate);
                    sheet.setColumnWidth(i, 6000);
                    cell.setCellStyle(stylewhiteCon);
                    Celldate ++ ;
                }
            }
        }

        //表头生成
        HSSFRow row2 = sheet.createRow(index++);
        row2.setHeight((short) 380); //设置行高
        int Cell = 0;
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row2.createCell(Cell);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            sheet.setColumnWidth(i, 6000);
            cell.setCellStyle(styleheards);
            Cell ++ ;
        }

        //内容
        for (int i = 0; i <data.size(); i++) {
            HSSFRow row = sheet.createRow(index);
            row.setHeight((short) 300); //设置行高
            Cell = 0;
            for (short j = 0; j < fields.length; j++) {
                String fieldName = fields[j];
                HSSFCell cell = row.createCell(Cell);
                cell.setCellStyle(stylewhiteCon);
                try {
                    Object value = "";
                    Map<String, Object> map=data.get(i);
                    value = map.get(fieldName);
                    if(value == null ) value = "";
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    // 数据类型都当作字符串简单处理
                    textValue = value.toString();
                    cell.setCellValue(textValue.trim());
                    Cell ++ ;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            index++;
        }
        HSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)
        ps.setVResolution((short)600);
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
        return workbook;
    }

    /**
     *
     * 生成 xlsx
     * 通用list<Map<String Object>>
     * 表格属性列名数组 （第一行标题）
     * 需要显示的表格属性列名数组 如果是javabean 必须和字段名字一致 如果为Map 必须为Map的key名字对应
     * 需要显示的数据集合,集合泛型支持两种，1：符合javabean风格的类的对象 2：Map类型。此方法支持的
     * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param name
     * @param title    表格标题
     * @param headers  表格标题名
     * @param fields   表格字段名
     * @param data 表格数据
     * @return
     */
    public SXSSFWorkbook exportExcelXLSX(String name, String title,String[] headers,String[]  fields,List<Map<String,Object>> data,String date) {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 生成一个表格
        SXSSFSheet sheet = workbook.createSheet("sheet1");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        // 生成一个样式
        CellStyle style = workbook.createCellStyle();
        // 生成一个字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);

        //白色背景色样式
        CellStyle stylewhite = workbook.createCellStyle();
        stylewhite.setWrapText(true);// 自动换行

        //白色背景色样式小标题
        CellStyle stylewhitele = workbook.createCellStyle();
        stylewhitele.setWrapText(true);// 自动换行

        //白色背景色样式headers
        CellStyle stylewhitehe= workbook.createCellStyle();
        stylewhitehe.setWrapText(true);// 自动换行

        //白色背景色样式内容
        CellStyle stylewhiteCon = workbook.createCellStyle();
        DataFormat formatcon = workbook.createDataFormat();
        stylewhiteCon.setDataFormat(formatcon.getFormat("@"));

        //标题-字体
        Font namefont = workbook.createFont();
        namefont.setFontName("宋体");
        namefont.setFontHeightInPoints((short) 15);// 字体大小

        //表头-字体
        Font headerfont = workbook.createFont();
        headerfont.setFontName("宋体");
        headerfont.setFontHeightInPoints((short) 10);// 字体大小
        headerfont.setBold(true);//设置字体加粗

        //表头-水平居中
        CellStyle styleheards = workbook.createCellStyle();
        styleheards.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());// 设置背景色
        styleheards.setFont(headerfont);//运用背景字体
        styleheards.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleheards.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        //表单-内容字体
        Font contentFont = workbook.createFont();
        contentFont.setFontHeightInPoints((short) 10);// 字体大小
        contentFont.setFontName("宋体");

        // 标题-把字体应用到当前的样式
        stylewhitehe.setFont(headerfont);
        stylewhite.setFont(namefont);
        stylewhite.setAlignment(HorizontalAlignment.CENTER);//水平居中
        stylewhite.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        //内容
        stylewhiteCon.setFont(contentFont);

        // 产生表格标题行
        SXSSFRow row0 = sheet.createRow(0);
        row0.setHeight((short) 500); //设置行高
        SXSSFCell cellname = row0.createCell(0);
        HSSFRichTextString textname = new HSSFRichTextString(name);
        cellname.setCellValue(textname);
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, headers.length-1);
        sheet.addMergedRegion(range);
        cellname.setCellStyle(stylewhite);
        int index=1;

        //统计时间生成
        if (headers.length > 2 ) {
            SXSSFRow row1 = sheet.createRow(index++);
            row1.setHeight((short) 380); //设置行高
            int celldate = 0;
            for (short i = 0; i < headers.length; i++) {
                if (i == headers.length-1) {
                    SXSSFCell cell = row1.createCell(celldate);
                    HSSFRichTextString text = new HSSFRichTextString(date);
                    cell.setCellValue("统计时间：" + text);
                    sheet.setColumnWidth(i, 7000);
                    cell.setCellStyle(stylewhiteCon);
                    celldate ++ ;
                }else{
                    SXSSFCell cell = row1.createCell(celldate);
                    sheet.setColumnWidth(i, 6000);
                    cell.setCellStyle(stylewhiteCon);
                    celldate ++ ;
                }
            }
        }

        //表头生成
        SXSSFRow row2 = sheet.createRow(index++);
        row2.setHeight((short) 380); //设置行高
        int celli = 0;
        for (short i = 0; i < headers.length; i++) {
            SXSSFCell cell = row2.createCell(celli);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            sheet.setColumnWidth(i, 6000);
            cell.setCellStyle(styleheards);
            celli ++ ;
        }

        //内容
        for (int i = 0; i <data.size(); i++) {
            SXSSFRow row = sheet.createRow(index);
            row.setHeight((short) 300); //设置行高
            celli = 0;
            for (short j = 0; j < fields.length; j++) {
                String fieldName = fields[j];
                SXSSFCell cell = row.createCell(celli);
                cell.setCellStyle(stylewhiteCon);
                try {
                    Object value = "";
                    Map<String, Object> map=data.get(i);
                    if(fieldName.equals("course_year")||fieldName.equals("instructor_year")){
                        value = StrUtil.patternDate(StrUtil.patternDate(String.valueOf(map.get(fieldName)),"yyyy"),"yyyy");
                    }else if(fieldName.equals("storage_time")){
                        value = StrUtil.patternDate(StrUtil.patternDate(String.valueOf(map.get(fieldName)),"yyyy-MM-dd"),"yyyy-MM-dd");
                    }else{
                        value = map.get(fieldName);
                    }

                    if(value == null ) value = "";
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    // 数据类型都当作字符串简单处理
                    textValue = value.toString();
                    cell.setCellValue(textValue.trim());
                    celli ++ ;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            index++;
        }
        PrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)
        ps.setVResolution((short)600);
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
        return workbook;
    }
    /**
     * 根据模板生成excel数据
     * 生成 xlsx
     * @param listdata 表格数据
     * @return
     */
    public SXSSFWorkbook luckExportExcelXLSX(List<Map<String, Object>> listdata) throws Exception {

        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 生成一个表格
        SXSSFSheet sheet = workbook.createSheet("sheet1");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(18);
        // 生成一个样式
        CellStyle style = workbook.createCellStyle();
        // 生成一个字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);
        // 把字体应用到当前的样式
        style.setFont(font);

        //表头-水平居中
        CellStyle styleheards = workbook.createCellStyle();
        styleheards.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());// 设置背景色
        styleheards.setFont(font);//运用背景字体
        styleheards.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleheards.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        styleheards.setBorderLeft(BorderStyle.THIN);
        styleheards.setBorderRight(BorderStyle.THIN);
        styleheards.setBorderTop(BorderStyle.THIN);
        styleheards.setBorderBottom(BorderStyle.THIN);



        // 生成一个字体
        Font fontCount = workbook.createFont();
        fontCount.setFontHeightInPoints((short) 12);
        //表头-水平居中
        CellStyle styleCount = workbook.createCellStyle();
        //styleCount.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());// 设置背景色
        styleCount.setFont(fontCount);//运用背景字体
        styleCount.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleCount.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        styleCount.setBorderLeft(BorderStyle.THIN);
        styleCount.setBorderRight(BorderStyle.THIN);
        styleCount.setBorderTop(BorderStyle.THIN);
        styleCount.setBorderBottom(BorderStyle.THIN);

        //
        int rowNum = 0;
        //创建表头
        SXSSFRow row1 = sheet.createRow(rowNum);
        row1.setHeightInPoints(28);//设置行高
        for (int i = 0; i < 8; i++) {
            SXSSFCell cell = row1.createCell(i);
            if (0==i){
                cell.setCellValue("签位");
            }
            if (1==i){
                cell.setCellValue("签号");
            }
            if (2==i){
                cell.setCellValue("姓名");
            }
            if (3==i){
                cell.setCellValue("是否原有车位");
            }
            if (4==i){
                cell.setCellValue("签位");
            }
            if (5==i){
                cell.setCellValue("签号");
            }
            if (6==i){
                cell.setCellValue("姓名");
            }
            if (7==i){
                cell.setCellValue("是否原有车位");
            }
            cell.setCellStyle(styleheards);//设置样式
        }

        rowNum++;//下一行
        for (Map<String, Object> listdatum : listdata) {
            String ld_id = String.valueOf(listdatum.get("ld_id"));
            String ld_no = null==String.valueOf(listdatum.get("ld_no"))||"null".equals(String.valueOf(listdatum.get("ld_no")))?"":String.valueOf(listdatum.get("ld_no"));
            String user_full = null==String.valueOf(listdatum.get("user_full"))||"null".equals(String.valueOf(listdatum.get("user_full")))?"":String.valueOf(listdatum.get("user_full"));
            String is_car = null==String.valueOf(listdatum.get("is_car"))||"null".equals(String.valueOf(listdatum.get("is_car")))?"":String.valueOf(listdatum.get("is_car"));
            if (listdatum.containsKey("ld_id") && Integer.valueOf(ld_id)<=11){
                Row row = sheet.createRow(rowNum);
                row.setHeightInPoints(28);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(ld_id);
                cell0.setCellStyle(styleCount);

                Cell cell1 = row.createCell(1);
                cell1.setCellValue(ld_no);
                cell1.setCellStyle(styleCount);

                Cell cell2 = row.createCell(2);
                cell2.setCellValue(user_full);
                cell2.setCellStyle(styleCount);

                Cell cell3 = row.createCell(3);
                if("1".equals(is_car.trim())){
                    cell3.setCellValue("否");
                }else if("2".equals(is_car.trim())){
                    cell3.setCellValue("是");
                }
                cell3.setCellStyle(styleCount);

                rowNum++;
            }else if (listdatum.containsKey("ld_id")&& Integer.valueOf(ld_id)>11 && Integer.valueOf(ld_id)<=22){
                Row row = sheet.getRow(rowNum-11);
                Cell cell0 = row.createCell(4);
                cell0.setCellValue(ld_id);
                cell0.setCellStyle(styleCount);

                Cell cell1 = row.createCell(5);
                cell1.setCellValue(ld_no);
                cell1.setCellStyle(styleCount);

                Cell cell2 = row.createCell(6);
                cell2.setCellValue(user_full);
                cell2.setCellStyle(styleCount);

                Cell cell3 = row.createCell(7);
                if("1".equals(is_car.trim())){
                    cell3.setCellValue("否");
                }else if("2".equals(is_car.trim())){
                    cell3.setCellValue("是");
                }
                cell3.setCellStyle(styleCount);

                rowNum++;
            }
        }

        return workbook;
    }

    public static void main(String[] args) {

        try {
            // 保存的位置

            File file = new File("d:/hssf.xlsx");
            FileOutputStream output = new FileOutputStream(file);

            String name = "name";
            String title = "title";
            String[] headers = new String[]{"开始","进行中","结束"};
            String[] fields = new String[]{"start","doing","end"};

            List<Map<String,Object>> data = new ArrayList<>();
            Map<String,Object> map = new HashMap<>();
            map.put("start","zouyeyue");
            map.put("doing","wu");
            map.put("end","shanchang");
            Map<String,Object> map1 = new HashMap<>();
            map1.put("start","zouyeyue1");
            map1.put("doing","wu1");
            map1.put("end","shanchang1");
            data.add(map);
            data.add(map1);

            String date = StrUtil.simpleDate("yyyy-MM-dd HH:mm:ss");

            Workbook sheets = new ExcelUtil().exportExcelXLSX(name, title, headers, fields, data, date);
            sheets.write(output);

            output.close();
            System.out.println("over! "+date);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
